//*********************************************************
//
//    Copyright (c) Microsoft. All rights reserved.
//    This code is licensed under the Apache License, Version 2.0.
//    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
//    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
//    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
//    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
//
//*********************************************************

using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Workflow.ComponentModel;
using System.Collections;
using System.Collections.Specialized;
using System.Workflow.ComponentModel.Design;
using System.ComponentModel.Design;
using System.Workflow.ComponentModel.Compiler;
using System.Data.Odbc;
using System.Xml;
using Microsoft.Research.ScientificWorkflow;
using System.IO;
using System.Data.SqlClient;
using WorkflowData;
using Microsoft.Research.ScientificWorkflow.NetCDF.CSharpAPI;

namespace Microsoft.Research.ScientificWorkflow.Activities
{
    /// <summary>
    /// This activity reads the HyperCube written to the DB and creates
    /// a HyperCube and the associated schema object. 
    /// The hypercube supports storing data against four dimensions x ,y ,z
    /// and t. All the vairables need not vary against all the dimensions.
    /// </summary>
    [Name("Hyper Cube DB Reader")]
    [Description("Reads the hyper cube data from the SQL instance.")]
    [WorkflowCategory("Database")]
    class HyperCubeDBReader : Activity
    {
        #region Constant Strings

        private const string DimensionTableQuery = "Select dmvalue from {0}";

        private const string DimensionalityQuery = "Select * from DimensionalityMap";

        private const string DataQuery = "Select {0} from HyperCubeDataTable";

        #endregion

        #region Dependency Properties

        public static DependencyProperty DatabaseConnectionProperty = DependencyProperty.Register("DatabaseConnection",
            typeof(SqlConnection), typeof(HyperCubeDBReader));

        /// <summary>
        /// Database Server Connection Object.
        /// </summary>
        [RequiredInputParam]
        [Name("Database Connection")]
        [Description("Connection to the database server.")]
        public SqlConnection DatabaseConnection
        {
            get { return ((SqlConnection)(base.GetValue(HyperCubeDBReader.DatabaseConnectionProperty))); }
            set { base.SetValue(HyperCubeDBReader.DatabaseConnectionProperty, value); }
        }

        public static DependencyProperty XDimensionTableNameProperty = DependencyProperty.Register("XDimensionTableName",
            typeof(string), typeof(HyperCubeDBReader));

        /// <summary>
        /// Names of the dimension tables to be used for getting X dimension
        /// values.
        /// </summary>
        [RequiredInputParam]
        [Name("X Dimension Table")]
        [Description("Name of the table for retrieveing X dimension values.")]
        public string XDimensionTableName
        {
            get { return ((string)(base.GetValue(HyperCubeDBReader.XDimensionTableNameProperty))); }
            set { base.SetValue(HyperCubeDBReader.XDimensionTableNameProperty, value); }
        }

        public static DependencyProperty XDimensionTypeProperty = DependencyProperty.Register("XDimensionType",
            typeof(string), typeof(HyperCubeDBReader));

        /// <summary>
        /// Type of the dimension values in the X dimension table.
        /// </summary>
        [RequiredInputParam]
        [Name("X Dimension Type")]
        [Description("Type of data to be stored in the X dimension.")]
        public string XDimensionType
        {
            get { return ((string)(base.GetValue(HyperCubeDBReader.XDimensionTypeProperty))); }
            set { base.SetValue(HyperCubeDBReader.XDimensionTypeProperty, value); }
        }

        public static DependencyProperty YDimensionTableNameProperty = DependencyProperty.Register("YDimensionTableName",
            typeof(string), typeof(HyperCubeDBReader));

        /// <summary>
        /// Names of the dimension table to be used for getting Y dimension
        /// values.
        /// </summary>
        [RequiredInputParam]
        [Name("Y Dimension Table")]
        [Description("Name of the table for retrieveing Y dimension values.")]
        public string YDimensionTableName
        {
            get { return ((string)(base.GetValue(HyperCubeDBReader.YDimensionTableNameProperty))); }
            set { base.SetValue(HyperCubeDBReader.YDimensionTableNameProperty, value); }
        }

        public static DependencyProperty YDimensionTypeProperty = DependencyProperty.Register("YDimensionType",
            typeof(string), typeof(HyperCubeDBReader));

        /// <summary>
        /// Type of the dimension values in the X dimension table.
        /// </summary>
        [RequiredInputParam]
        [Name("Y Dimension Type")]
        [Description("Type of data to be stored in the Y dimension.")]
        public string YDimensionType
        {
            get { return ((string)(base.GetValue(HyperCubeDBReader.YDimensionTypeProperty))); }
            set { base.SetValue(HyperCubeDBReader.YDimensionTypeProperty, value); }
        }

        public static DependencyProperty ZDimensionTableNameProperty = DependencyProperty.Register("ZDimensionTableName",
            typeof(string), typeof(HyperCubeDBReader));

        /// <summary>
        /// Names of the dimension table to be used for getting Z dimension
        /// values.
        /// </summary>
        [RequiredInputParam]
        [Name("Z Dimension Table")]
        [Description("Name of the table for retrieveing Z dimension values.")]
        public string ZDimensionTableName
        {
            get { return ((string)(base.GetValue(HyperCubeDBReader.ZDimensionTableNameProperty))); }
            set { base.SetValue(HyperCubeDBReader.ZDimensionTableNameProperty, value); }
        }

        public static DependencyProperty ZDimensionTypeProperty = DependencyProperty.Register("ZDimensionType",
            typeof(string), typeof(HyperCubeDBReader));

        /// <summary>
        /// Type of the dimension values in the Z dimension table.
        /// </summary>
        [RequiredInputParam]
        [Name("Z Dimension Type")]
        [Description("Type of data to be stored in the Z dimension.")]
        public string ZDimensionType
        {
            get { return ((string)(base.GetValue(HyperCubeDBReader.ZDimensionTypeProperty))); }
            set { base.SetValue(HyperCubeDBReader.ZDimensionTypeProperty, value); }
        }

        public static DependencyProperty TDimensionTableNameProperty = DependencyProperty.Register("TDimensionTableName",
            typeof(string), typeof(HyperCubeDBReader));

        /// <summary>
        /// Names of the dimension table to be used for getting T dimension
        /// values.
        /// </summary>
        [RequiredInputParam]
        [Name("T Dimension Table")]
        [Description("Name of the table for retrieveing T dimension values.")]
        public string TDimensionTableName
        {
            get { return ((string)(base.GetValue(HyperCubeDBReader.TDimensionTableNameProperty))); }
            set { base.SetValue(HyperCubeDBReader.TDimensionTableNameProperty, value); }
        }

        public static DependencyProperty TDimensionTypeProperty = DependencyProperty.Register("TDimensionType",
            typeof(string), typeof(HyperCubeDBReader));

        /// <summary>
        /// Type of the dimension values in the T dimension table.
        /// </summary>
        [RequiredInputParam]
        [Name("T Dimension Type")]
        [Description("Type of data to be stored in the T dimension.")]
        public string TDimensionType
        {
            get { return ((string)(base.GetValue(HyperCubeDBReader.TDimensionTypeProperty))); }
            set { base.SetValue(HyperCubeDBReader.TDimensionTypeProperty, value); }
        }

        public static DependencyProperty DataTableNameProperty = DependencyProperty.Register("DataTableName",
            typeof(string), typeof(HyperCubeDBReader));

        /// <summary>
        /// Names of the table from where the data is to be read.
        /// </summary>
        [RequiredInputParam]
        [Name("Data Table")]
        [Description("Name of the table for retrieveing data values.")]
        public string DataTableName
        {
            get { return ((string)(base.GetValue(HyperCubeDBReader.DataTableNameProperty))); }
            set { base.SetValue(HyperCubeDBReader.DataTableNameProperty, value); }
        }

        public static DependencyProperty VariableDetailsProperty = DependencyProperty.Register("VariableDetails",
            typeof(string), typeof(HyperCubeDBReader));

        /// <summary>
        /// Names of the table from where the data is to be read.
        /// </summary>
        [RequiredInputParam]
        [Name("Variable Details")]
        [Description("Variables to be read from the data table.")]
        public string VariableDetails
        {
            get { return ((string)(base.GetValue(HyperCubeDBReader.VariableDetailsProperty))); }
            set { base.SetValue(HyperCubeDBReader.VariableDetailsProperty, value); }
        }
        
        public static DependencyProperty HyperCubeProperty =
            DependencyProperty.Register("HyperCube", typeof(HyperCube), typeof(HyperCubeDBReader));

        /// <summary>
        /// Hypercube generated with data read from the DB
        /// </summary>
        [OutputParam]
        [Name("HyperCube Object")]
        [Description("HyperCube object generated by this activity as output.")]
        public HyperCube HyperCube
        {
            get
            {
                return ((HyperCube)(base.GetValue(HyperCubeDBReader.HyperCubeProperty)));
            }
            set
            {
                base.SetValue(HyperCubeDBReader.HyperCubeProperty, value);
            }
        }

        #endregion

        #region Overridden Methods

        /// <summary>
        /// Read the data for the hypercube from the database and create a new hypercube
        /// object to set as the output parameter.
        /// </summary>
        /// <param name="executionContext"></param>
        /// <returns></returns>
        protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
        {
            if (null == this.DatabaseConnection || 
                (System.Data.ConnectionState.Open != this.DatabaseConnection.State))
                return ActivityExecutionStatus.Faulting;

            string[] names, types;
            ParseVariables(this.VariableDetails, out names, out types);

            if (null == names)
            {
                System.Console.WriteLine("No variables specified for reading in the HyperCube");
                return ActivityExecutionStatus.Faulting;
            }

            HyperCubeSchema schema = new HyperCubeSchema(names.Length);

            PopulateSchema(ref names, ref types, ref schema);

            HyperCube cube = GenerateCube(ref schema);

            // Get the dimensionality information for the variables for which
            // data is to be read from the cube.
            ReadDimensionality(ref cube);

            // Read the variable data from the database
            ReadData(ref cube);

            this.HyperCube = cube;

            return ActivityExecutionStatus.Closed;
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// This method reads the data values for the variables sepecified
        /// in the argument list from the db and stores the values read
        /// in the hypercube at the hypercubedatapoints.
        /// </summary>
        /// <param name="cube"></param>
        private void ReadData(ref HyperCube cube)
        {
            // For each variable read the values form the DB then depending
            // on the dimensionality set the axis lengths and assign the values
            // in the hypercube.
            for (int i = 0; i < cube.Schema.ColumnCount; ++i)
            {
                int xLen = (true == cube.Schema.HasDimension(i, HyperCube.Axis.X)) ?
                    cube.XDimensionValues.Count : 1;
                int yLen = (true == cube.Schema.HasDimension(i, HyperCube.Axis.Y)) ?
                    cube.YDimensionValues.Count : 1;
                int zLen = (true == cube.Schema.HasDimension(i, HyperCube.Axis.Z)) ?
                    cube.ZDimensionValues.Count : 1;
                int tLen = (true == cube.Schema.HasDimension(i, HyperCube.Axis.T)) ?
                    cube.TDimensionValues.Count : 1;

                string sqlQuery = string.Format(HyperCubeDBReader.DataQuery, cube.Schema.GetColumnName(i));

                using (SqlDataReader reader = DatabaseHelper.ExecuteReaderQuery(this.DatabaseConnection, sqlQuery, null))
                {
                    System.Diagnostics.Debug.Assert(null != reader);
                    for (int x = 0; x < xLen; ++x)
                    {
                        for (int y = 0; y < yLen; ++y)
                        {
                            for (int z = 0; z < zLen; ++z)
                            {
                                for (int t = 0; t < tLen; ++t)
                                {
                                    reader.Read();
                                    cube.SetData(reader[0], x, y, z, t, i);
                                }
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// For all the variables for which the data values are to be read this method
        /// sets the dimensionality in the cube.
        /// </summary>
        /// <param name="cube"></param>
        private void ReadDimensionality(ref HyperCube cube)
        {
            using (SqlDataReader reader = DatabaseHelper.ExecuteReaderQuery(this.DatabaseConnection,
                HyperCubeDBReader.DimensionalityQuery, null))
            {

                if (null == reader)
                    return;

                while (reader.Read())
                {
                    int index = cube.Schema.LookupColumnIndex(reader[0].ToString());
                    int dimensionalityVal = Convert.ToInt32(reader[1]);

                    if (0 != (dimensionalityVal & 1))
                        cube.Schema.SetDimension(index, HyperCube.Axis.X);
                    if (0 != (dimensionalityVal & 2))
                        cube.Schema.SetDimension(index, HyperCube.Axis.Y);
                    if (0 != (dimensionalityVal & 4))
                        cube.Schema.SetDimension(index, HyperCube.Axis.Z);
                    if (0 != (dimensionalityVal & 8))
                        cube.Schema.SetDimension(index, HyperCube.Axis.T);
                }
            }
        }

        /// <summary>
        /// This method generates the hyper cube object using the associated 
        /// schema object.
        /// </summary>
        /// <param name="schema"></param>
        /// <returns></returns>
        private HyperCube GenerateCube(ref HyperCubeSchema schema)
        {
            List<object> xDimValues = null, yDimValues = null, zDimValues = null, tDimValues = null;

            if (!string.IsNullOrEmpty(this.XDimensionTableName))
                xDimValues = ReadDimensionData(this.XDimensionTableName);

            if (!string.IsNullOrEmpty(this.YDimensionTableName))
                yDimValues = ReadDimensionData(this.YDimensionTableName);

            if (!string.IsNullOrEmpty(this.ZDimensionTableName))
                zDimValues = ReadDimensionData(this.ZDimensionTableName);

            if (!string.IsNullOrEmpty(this.TDimensionTableName))
                tDimValues = ReadDimensionData(this.TDimensionTableName);

            HyperCube cube = new HyperCube(schema, (null == xDimValues ? 1 : xDimValues.Count),
                (null == yDimValues ? 1 : yDimValues.Count), (null == zDimValues ? 1 : zDimValues.Count),
                (null == tDimValues ? 1 : tDimValues.Count));

            cube.XDimensionValues = xDimValues;
            cube.YDimensionValues = yDimValues;
            cube.ZDimensionValues = zDimValues;
            cube.TDimensionValues = tDimValues;

            return cube;
        }

        /// <summary>
        /// This method populates the schema object after reading the values from the
        /// DB.
        /// </summary>
        /// <param name="?"></param>
        private void PopulateSchema(ref string[] variableNames, ref string[] variableTypes, ref HyperCubeSchema schema)
        {
            // The dimension names that are being added here are dummy. The
            // DB does not store them as of now.
            if (!string.IsNullOrEmpty(this.XDimensionTableName))
            {
                schema.XDimensionName = "XDimension";
                schema.XDimensionType = TypeMapper.GetClrType(this.XDimensionType);
            }

            if (!string.IsNullOrEmpty(this.YDimensionTableName))
            {
                schema.YDimensionName = "YDimension";
                schema.YDimensionType = TypeMapper.GetClrType(this.YDimensionType);
            }

            if (!string.IsNullOrEmpty(this.ZDimensionTableName))
            {
                schema.ZDimensionName = "ZDimension";
                schema.ZDimensionType = TypeMapper.GetClrType(this.ZDimensionType);
            }

            if (!string.IsNullOrEmpty(this.TDimensionTableName))
            {
                schema.TDimensionName = "TDimension";
                schema.TDimensionType = TypeMapper.GetClrType(this.TDimensionType);
            }

            for (int i = 0; i < variableNames.Length; ++i)
            {
                schema.SetName(i, variableNames[i]);
                schema.SetType(i, TypeMapper.GetClrType(variableTypes[i]));
            }
        }

        /// <summary>
        /// This method tokenizes the variable details string to get the
        /// variable names and types to be read from the data table.
        /// </summary>
        /// <param name="variableDetails"></param>
        /// <param name="names"></param>
        /// <param name="types"></param>
        private void ParseVariables(string variableDetails, out string[] names, out string[] types)
        {
            if(string.IsNullOrEmpty(variableDetails))
            {
                names = null;
                types = null;
                return;
            }

            string[] v = (variableDetails).Split(new char[] { ',' });
            if (v.Length % 2 != 0)
            {
                names = null;
                types = null;
                return;
            }

            names = new string[v.Length / 2];
            types = new string[v.Length / 2];
            for (int i = 0,j=0; i < v.Length; i += 2, j++)
            {
                names[j] = v[i];
                types[j] = v[i + 1];
            }
        }

        /// <summary>
        /// This method reads the dimension values from the database.
        /// </summary>
        /// <param name="dimensionTableName"></param>
        private List<object> ReadDimensionData(string dimensionTableName)
        {
            string sqlQuery = string.Format(HyperCubeDBReader.DimensionTableQuery, dimensionTableName);

            SqlDataReader reader = DatabaseHelper.ExecuteReaderQuery(this.DatabaseConnection, sqlQuery, null);
            if (null == reader)
                return null;

            List<object> dimValues = new List<object>();
            while (reader.Read())
            {
                dimValues.Add(reader[0]);
            }
            reader.Close();
            return dimValues;
        }

        #endregion
    }
}
